import os
import re
import math
import json
from jinja2 import Environment, FileSystemLoader
from openpyxl import load_workbook
from loguru import logger
from pathlib import Path
from modules.context import create_global_context

script_dir = os.path.dirname(os.path.abspath(__file__))

class ExcelParser:
    mtype2Length = {
        "bit": 1,
        "int16_t": 2,
        "int32_t": 4,
        "uint16_t": 2,
        "uint16_t(bits)": 2,
        "enum16": 2,
        "enum32": 4,
        "uint32_t(bits)": 4,
        "uint32_t": 4,
        "UINT32": 4,
        "INT16": 2,
        "INT32": 4,
    }

    mwb = None

    def __init__(self):
        self.mctx = create_global_context()
        self.mtmp = Path(os.path.join(self.mctx.work_dir, "..", "templates"))
        self.menv = Environment(loader=FileSystemLoader(self.mtmp.absolute()))
        pass

    def openExcel(self, filePath):
        if os.path.exists(filePath) is False:
            return False
        
        self.mfilePath = filePath
        self.mwb = load_workbook(filename=self.mfilePath, read_only=True)
        return True if self.mwb is not None else False
    
    def formatCheck(self):
        if self.mwb is None:
            return False

        for ws in self.mwb.worksheets:
            logger.info("开始检测工作表格: " + ws.title)
            # 1. 确保A1为MdIHeader
            if ws['A1'].value != "MdIHeader":
                logger.error("Error format, check sheet: " + ws.title + " ws['A1'] is not MdIHeader, read and check the readme for more details to use excel2modbus")
                return False
            # 2. 确保有 MdI0	MdI1	MdI2	MdI3	MdI5 这些列
            # MdI0: 操作权限（必填）
            # MdI1: 寄存器地址（必填）
            # MdI2: 数据类型（必填）
            # MdI3: 长度（必填）
            # MdI5: 命名（英文）(必填)
            firstSet = [cell.value for cell in ws[1]]
            checkSet = ["MdI0", "MdI1", "MdI2", "MdI3", "MdI5"]
            if len(firstSet) != len(set(firstSet)):
                logger.error("Error format, check sheet: " + ws.title + " ws[1] have repeating element, read and check the readme for more details to use excel2modbus")
                return False
            if set(checkSet).issubset(set(firstSet)) is False:
                logger.error("Error format, check sheet: " + ws.title + " without essential element of [\"MdI0\", \"MdI1\", \"MdI2\", \"MdI3\", \"MdI5\", \"MdI12\", \"MdI13\", \"MdI14\", \"MdI15\"]")
                return False
            logger.info("检测表格完成: " + ws.title)

        return True
    
    def _parseTableRegisters(self, ws, enums:list, struct_bitss:list):
        registers = list()
        reserve_counter = 0

        for cells in enumerate(ws, start=1):
            if cells[1][0].value == "Register" or cells[1][0].value == "Coil" or cells[1][0].value == "Register(bits)":
                register = dict()
                # 1. 寄存器权限
                register["privilage"] = "RO" if cells[1][self.mmdiMap["MdI0"]].value == "R" or cells[1][self.mmdiMap["MdI0"]].value == "RO" else "RW"
                # 2. 寄存器地址
                # 如果地址为空，则跳过
                if cells[1][self.mmdiMap["MdI1"]].value is None:
                    continue
                register["address"] = cells[1][self.mmdiMap["MdI1"]].value
                # 3. 寄存器类型
                register["type"] = cells[1][self.mmdiMap["MdI2"]].value
                # 4. 依据类型的长度
                if (register["type"] == "uint16_t" or register["type"] == "int16_t" or 
                    register["type"] == "uint32_t" or register["type"] == "int32_t"):
                    if (cells[1][self.mmdiMap["MdI3"]].value is None or cells[1][self.mmdiMap["MdI3"]].value == "1" or
                        cells[1][self.mmdiMap["MdI3"]].value == "/" or cells[1][self.mmdiMap["MdI3"]].value == "\\"):
                        register["length"] = 1
                    else:
                        register["length"] = self._valueCell2Int(cells[1][self.mmdiMap["MdI3"]].value)
                elif (register["type"] == "enum16" or register["type"] == "enum32" or
                      register["type"] == "uint16_t(bits)" or register["type"] == "uint32_t(bits)"):
                        register["length"] = 1
                else:
                    register["length"] = self._valueCell2Int(cells[1][self.mmdiMap["MdI3"]].value)
                # 5. 寄存器名称
                register["name"] = cells[1][self.mmdiMap["MdI5"]].value
                if register["name"] == "reserve" or register["name"] == None:
                    register["name"] = "reserve" + str(reserve_counter)
                    reserve_counter += 1
                # 6. 寄存器的归类
                if (cells[1][0].value == "Register" or cells[1][0].value == "Register(bits)") and (register["privilage"] == "RO" or register["privilage"] == "R"):
                    register["class"] = "input_register"
                elif (cells[1][0].value == "Register" or cells[1][0].value == "Register(bits)") and register["privilage"] == "RW":
                    register["class"] = "holding_register"
                elif cells[1][0].value == "Coil" and (register["privilage"] == "RO" or register["privilage"] == "R"):
                    register["class"] = "input_coil"
                elif cells[1][0].value == "Coil" and register["privilage"] == "RW":
                    register["class"] = "holding_coil"

                # 7. 说明
                if self.mmdiMap.get("MdI4") != None and cells[1][self.mmdiMap["MdI4"]].value != None:
                    register["description"] = cells[1][self.mmdiMap["MdI4"]].value
                else:
                    register["description"] = ""
                # 8. 详细解释（可选）
                if self.mmdiMap.get("MdI6") != None and cells[1][self.mmdiMap["MdI6"]].value != None:
                    register["details"] = cells[1][self.mmdiMap["MdI6"]].value
                # 9. 该寄存器取值所关联的枚举类型（可选）
                if cells[1][self.mmdiMap["MdI2"]].value == "uint16_t(bits)" or cells[1][self.mmdiMap["MdI2"]].value == "uint32_t(bits)":
                    if self.mmdiMap.get("MdI7") != None and cells[1][self.mmdiMap["MdI7"]].value != None:
                        for struct in struct_bitss["structs"]:
                            if struct["name"] == cells[1][self.mmdiMap["MdI7"]].value:
                                register["struct"] = struct["name"]
                                register["struct_elements"] = struct["elements"]
                                break
                    else:
                        register["struct_elements"] = None
                else:
                    if self.mmdiMap.get("MdI7") != None and cells[1][self.mmdiMap["MdI7"]].value != None:
                        for enum in enums["enums"]:
                            if enum["name"] == cells[1][self.mmdiMap["MdI7"]].value:
                                register["enum"] = enum["name"]
                                break
                    # 10. 关联寄存器和枚举取值定义（可选）
                    if self.mmdiMap.get("MdI7") != None and cells[1][self.mmdiMap["MdI7"]].value != None:
                        for enum in enums["enums"]:
                            if enum["name"] == cells[1][self.mmdiMap["MdI7"]].value:
                                register["enum_name"] = enum["name"]
                                register["enum_elements"] = enum["elements"]
                                break
                    else:
                        register["enum_elements"] = None
                registers.append(register)
                
        # print(json.dumps(registers, indent=4, ensure_ascii=False))
        return registers
                            
    
    def _parseTableEnums(self, ws):
        enums = dict()
        enums["enums"] = list()
        enums_set = set()

        for cells in enumerate(ws, start=1):
            if cells[1][0].value == "Register" or cells[1][0].value == "Coil":
                enum = dict()
                if (cells[1][self.mmdiMap["MdI2"]].value == "enum16" or 
                    cells[1][self.mmdiMap["MdI2"]].value == "enum32" or
                    cells[1][self.mmdiMap["MdI2"]].value == "bit"):
                    # 2.1 生成枚举类型
                    if self.mmdiMap.get("MdI7") != None and cells[1][self.mmdiMap["MdI7"]].value != None:
                        if cells[1][self.mmdiMap["MdI7"]].value in enums_set:
                            continue
                        
                        # 2.2 枚举类型名称
                        enum["name"] = cells[1][self.mmdiMap["MdI7"]].value
                        enum["elements"] = list()
                        # 2.2 枚举类型值定义
                        if self.mmdiMap.get("MdI8") != None and cells[1][self.mmdiMap["MdI8"]].value != None:
                            for line in cells[1][self.mmdiMap["MdI8"]].value.splitlines():
                                element = dict()
                                key, value = line.split(":")
                                key = key.strip()
                                value = value.strip()
                                element["name"] = key
                                element["value"] = value
                                element["description"] = None
                                enum["elements"].append(element)
                        # 2.3 枚举类型说明
                        if self.mmdiMap.get("MdI9") != None and cells[1][self.mmdiMap["MdI9"]].value != None:
                            for line in cells[1][self.mmdiMap["MdI9"]].value.splitlines():
                                key, value = line.split(":")
                                key = key.strip()
                                value = value.strip()
                                for e in enum["elements"]:
                                    if e["name"] == key:
                                        e["description"] = value
                        enums_set.add(enum["name"])
                        enums["enums"].append(enum)
        # print(enums)
        return enums

    def _parseTableStrcutBitss(self, ws):
        struct_bitss = dict()
        struct_bitss["structs"] = list()
        struct_bits_set = set()
        
        for cells in enumerate(ws, start=1):
            if cells[1][0].value == "Register(bits)":
                if (cells[1][self.mmdiMap["MdI2"]].value == "uint16_t(bits)" or 
                    cells[1][self.mmdiMap["MdI2"]].value == "uint32_t(bits)"):
                    # 生成bits结构体类型
                    if self.mmdiMap.get("MdI6") is not None and cells[1][self.mmdiMap["MdI6"]].value is not None:
                        struct_description = cells[1][self.mmdiMap["MdI6"]].value
                    if self.mmdiMap.get("MdI7") is not None and cells[1][self.mmdiMap["MdI7"]].value is not None:
                        struct_name = cells[1][self.mmdiMap["MdI7"]].value
                        if struct_name not in struct_bits_set:
                            # 新的结构体类型
                            struct_bits = {"name": struct_name, "elements": []}
                            struct_bitss["structs"].append(struct_bits)
                            struct_bits_set.add(struct_name)
                        else:
                            # 找到已有的结构体
                            struct_bits = next(s for s in struct_bitss["structs"] if s["name"] == struct_name)
                        
                        # 创建元素
                        element = {}
                        if self.mmdiMap.get("MdI11") is not None and cells[1][self.mmdiMap["MdI11"]].value is not None:
                            for line in cells[1][self.mmdiMap["MdI11"]].value.splitlines():
                                key_value = line.split(":")
                                if len(key_value) == 2:
                                    element["name"] = key_value[0].strip()
                                    element["description"] = key_value[1].strip()
                        if self.mmdiMap.get("MdI12") is not None and cells[1][self.mmdiMap["MdI12"]].value is not None:
                            element["bit_start"] = cells[1][self.mmdiMap["MdI12"]].value
                        if self.mmdiMap.get("MdI13") is not None and cells[1][self.mmdiMap["MdI13"]].value is not None:
                            element["bit_len"] = cells[1][self.mmdiMap["MdI13"]].value
                        # 创建元素的枚举类型
                        enum = list()
                        if self.mmdiMap.get("MdI8") != None and cells[1][self.mmdiMap["MdI8"]].value != None:
                            for line in cells[1][self.mmdiMap["MdI8"]].value.splitlines():
                                ele = dict()
                                key, value = line.split(":")
                                key = key.strip()
                                value = value.strip()
                                ele["name"] = key
                                ele["value"] = value
                                ele["description"] = None
                                enum.append(ele)
                        if self.mmdiMap.get("MdI9") != None and cells[1][self.mmdiMap["MdI9"]].value != None:
                            for line in cells[1][self.mmdiMap["MdI9"]].value.splitlines():
                                key, value = line.split(":")
                                key = key.strip()
                                value = value.strip()
                                for e in enum:
                                    if e["name"] == key:
                                        e["description"] = value
                        # 关联元素和枚举类型
                        element["enum"] = enum
                        # 添加元素到结构体
                        struct_bits["elements"].append(element)
        # print(struct_bitss)
        return struct_bitss


    """
        将Excel地址转换为十进制

        :param address: Excel地址，例如"100"或"0x100"
    """
    def _valueCell2Int(self, value):
        if isinstance(value, str):
            # 检查是否是十六进制格式（以0x开头）
            if value.startswith('0x'):
                return int(value, 16)  # 转换为十进制
            try:
                return int(value)  # 尝试转换为十进制
            except ValueError:
                return value  # 返回原始值
        else:
            return value

    def _associateRegionWithRegister(self, registers: dict, regions: list, reg_class: str):
        for region in regions[reg_class + "s"]:
            region["registers"] = list()
            for register in registers:
                if register["class"] != reg_class:
                    continue

                if (self._valueCell2Int(region["start"]) <= self._valueCell2Int(register["address"]) and 
                    self._valueCell2Int(register["address"]) <= self._valueCell2Int(region["end"])):
                    region["registers"].append(register)

    """
        计算寄存器地址范围

        @param ws: worksheet 对象
        @param MdiHeader:  寄存器类型或者线圈类型
        @param MdI0: 寄存器/线圈的读写权限
        @param regTabType: 地址编址类型
        @param regTabIdx: 寄存器表索引
    
    """
    def _parseRegions(self, ws, type: str, privilage: str):
        regions = list()
        
        region = dict()
        region["start"] = None
        region["end"] = None

        for cells in enumerate(ws, start=1):
            if cells[1][0].value == type and \
                (cells[1][self.mmdiMap["MdI0"]].value in ["R", "RO"] if privilage == "RO" else cells[1][self.mmdiMap["MdI0"]].value == privilage) and \
                cells[1][self.mmdiMap["MdI1"]].value != None:
                # 确定寄存器类型，进行过滤

                if cells[1][0].value == "Register" or cells[1][0].value == "Register(bits)":
                    registerSize = int(self.mtype2Length[cells[1][self.mmdiMap["MdI2"]].value] * self._valueCell2Int(cells[1][self.mmdiMap["MdI3"]].value) / 2)
                else:
                    registerSize = 1 * self._valueCell2Int(cells[1][self.mmdiMap["MdI3"]].value)
                
                if region["start"] != None and region["end"] != None:
                    if self._valueCell2Int(region["end"]) + 1 == self._valueCell2Int(cells[1][self.mmdiMap["MdI1"]].value):
                        region["end"] = f"0x{((self._valueCell2Int(cells[1][self.mmdiMap['MdI1']].value) + registerSize) - 1):04x}"
                    elif self._valueCell2Int(region["start"]) - registerSize == self._valueCell2Int(cells[1][self.mmdiMap["MdI1"]].value):
                        region["start"] = f"0x{(self._valueCell2Int(cells[1][self.mmdiMap['MdI1']].value)):04x}"
                    else:
                        regions.append(region)
                        region = dict()
                        region["start"] = f"0x{(self._valueCell2Int(cells[1][self.mmdiMap['MdI1']].value)):04x}"
                        region["end"] = f"0x{(self._valueCell2Int(cells[1][self.mmdiMap['MdI1']].value) + registerSize - 1):04x}"
                else:
                    if region["start"] is None:
                        region["start"] = f"0x{(self._valueCell2Int(cells[1][self.mmdiMap['MdI1']].value)):04x}"
                    if region["end"] is None:
                        region["end"] = f"0x{(self._valueCell2Int(cells[1][self.mmdiMap['MdI1']].value) + registerSize - 1):04x}"
        
    
        if region["start"] != None and region["end"] != None:
            regions.append(region)

        logger.debug(json.dumps(regions, indent=4))
        return regions

    def _parserWorkSheet(self, ws):
            wsInfos = dict()
            # 表格基本信息
            wsInfos["ws_name"] = ws.title

            wsInfos["table"] = dict()
            # 寄存器枚举信息
            wsInfos["table"]["enums"] = self._parseTableEnums(ws=ws)

            # 寄存器位域信息
            wsInfos["table"]["struct_bitss"] = self._parseTableStrcutBitss(ws=ws)

            # 寄存器线圈信息
            wsInfos["table"]["registers"] = self._parseTableRegisters(ws=ws, enums=wsInfos["table"]["enums"], struct_bitss=wsInfos["table"]["struct_bitss"])
            
            # print(wsInfos)
            # 表格分区信息
            wsInfos["table"]["regions"] = dict()
            wsInfos["table"]["regions"]["input_coils"] = self._parseRegions(ws=ws, type="Coil", privilage="RO")
            wsInfos["table"]["regions"]["holding_coils"] = self._parseRegions(ws=ws, type="Coil", privilage="RW")
            wsInfos["table"]["regions"]["input_registers"] = []
            wsInfos["table"]["regions"]["input_registers"].extend(self._parseRegions(ws=ws, type="Register", privilage="RO"))
            wsInfos["table"]["regions"]["input_registers"].extend(self._parseRegions(ws=ws, type="Register(bits)", privilage="RO"))
            # wsInfos["table"]["regions"]["input_registers"] = self._parseRegions(ws=ws, type="Register", privilage="RO")
            # wsInfos["table"]["regions"]["input_registers"] = self._parseRegions(ws=ws, type="Register(bits)", privilage="RO")
            wsInfos["table"]["regions"]["holding_registers"] = []
            wsInfos["table"]["regions"]["holding_registers"].extend(self._parseRegions(ws=ws, type="Register", privilage="RW"))
            wsInfos["table"]["regions"]["holding_registers"].extend(self._parseRegions(ws=ws, type="Register(bits)", privilage="RW"))
            # wsInfos["table"]["regions"]["holding_registers"] = self._parseRegions(ws=ws, type="Register", privilage="RW")
            # wsInfos["table"]["regions"]["holding_registers"] = self._parseRegions(ws=ws, type="Register(bits)", privilage="RW")

            # 关联区域和寄存器
            self._associateRegionWithRegister(registers=wsInfos["table"]["registers"], regions=wsInfos["table"]["regions"], reg_class="input_coil")
            self._associateRegionWithRegister(registers=wsInfos["table"]["registers"], regions=wsInfos["table"]["regions"], reg_class="holding_coil")
            self._associateRegionWithRegister(registers=wsInfos["table"]["registers"], regions=wsInfos["table"]["regions"], reg_class="input_register")
            self._associateRegionWithRegister(registers=wsInfos["table"]["registers"], regions=wsInfos["table"]["regions"], reg_class="holding_register")
                
            # logger.debug(json.dumps(wsInfos, indent=4))
            # print(wsInfos)
            return wsInfos


    def parse(self):
        excel = dict()
        excel["file"] = self.mfilePath

        if self.mwb is None:
            return None
        
        worksheets = list()
        for ws in self.mwb.worksheets:
            worksheet = dict()
            logger.info("开始解析工作表格: " + ws.title)
            self.mmdiMap = { cell.value: index for index, cell in enumerate(ws[1]) if cell.value != "MdIHeader" }
            worksheet = self._parserWorkSheet(ws=ws)
            worksheets.append(worksheet)
            logger.info("解析工作表格完成: " + ws.title)

        excel["worksheets"] = worksheets
        # print(excel)
        return excel

if __name__ == "__main__":
    paser = ExcelParser()
    paser.openExcel(filePath=r"D:\Users\BinWatson\Desktop\ModbusTemplate.xlsm")
    paser.formatCheck()
    # paser.generateHeadFile()
    # paser.generateAilgeSlaveSource()
